package com.xmy.cultivate.mapper;

import com.baomidou.mybatisplus.core.metadata.IPage;
import com.xmy.cultivate.entity.Recruit;
import com.xmy.cultivate.entity.RecruitList;
import com.xmy.cultivate.entity.TeacherPoint;
import com.xmy.cultivate.entity.TryJob;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.List;

/**
 * <p>
 * 试岗 Mapper 接口
 * </p>
 *
 * @author hpiggy
 * @since 2024-12-08
 */
public interface TryJobMapper extends BaseMapper<TryJob> {

    @Select({"<script>",
            "SELECT t1.*,r1.name,sn.type as department_type,dm.`name` as department_name  FROM adms_try_job AS t1 " ,
            "LEFT JOIN adms_recruit AS r1 ON t1.recruit_id = r1.id " ,
            "LEFT JOIN adms_station_needs as sn ON t1.station_needs_id = sn.id" ,
            "LEFT JOIN department as dm ON sn.department_id = dm.id " ,
            "WHERE " ,
            "1=1" ,
//            "<if test= 'type!=null and type == 0 and type!=&apos;&apos; '>",
//            "t1.pass_type = #{type} AND  t1.is_arrive_job = 0" ,
//            "</if>" ,
            "<if test= 'type!=null and type!=&apos;&apos; '>",
            "<choose>",
            "<when test='type == 0 '>",
            "AND t1.pass_type = #{type} AND t1.is_arrive_job = 0",
            "</when>",
            "<when test='type == 1 or type == 2'>",
            "AND t1.pass_type = #{type} AND t1.is_arrive_job = 0",
            "</when>",
            "<otherwise>",
            "AND t1.pass_type = 0 AND t1.is_arrive_job = 1",
            "</otherwise>",
            "</choose>",
            "</if>" ,
            "<if test='schoolId!=null and schoolId!=&apos;&apos; '>",
            "AND t1.school_id IN ( ${schoolId} )",
            "</if>",

            "<if test='subjectId!=null and subjectId!=&apos;&apos; '>",
            "AND t1.subjects_id IN ( ${subjectId} )",
            "</if>",

            "<if test='name!=null and name!=&apos;&apos; '>",
            "AND r1.name LIKE CONCAT('%', #{name}, '%')",
            "</if>",
            "<if test='startDate!=null and startDate!=&apos;&apos; and endDate!=null and endDate!=&apos;&apos;'>",
            "AND t1.try_date &gt;= #{startDate} AND t1.try_date &lt;= #{endDate}",
            "</if>",
            "ORDER BY t1.try_date DESC ",
            "</script>"})
    List<TryJob> queryTryJobList(IPage<TryJob> page, @Param("startDate") String startDate, @Param("endDate") String endDate, @Param("type") String type, @Param("name") String name, @Param("schoolId") String schoolId, @Param("subjectId") String subjectId);

//原来
/*
    @Select({"<script>",
            "SELECT t1.*,r1.name  FROM adms_try_job AS t1 " ,
            "LEFT JOIN adms_recruit AS r1 ON t1.recruit_id = r1.id " ,
            "WHERE " ,
            "<if test= 'type == 0 and isReview == 0'>",
            "t1.pass_type = #{type} AND (try_satisficing =0 OR is_appraisal =0)" ,
            "</if>" ,
            "<if test= 'type == 1 or type == 2'>",
            "t1.pass_type = #{type} " ,
            "</if>" ,
            "<if test= 'type == 0 and isReview == 1'>",
            "t1.pass_type = #{type} AND is_appraisal = #{isReview} AND try_satisficing IN(1,2,3)" ,
            "</if>" ,
            "<if test='name!=null and name!=&apos;&apos; '>",
            "AND r1.name LIKE CONCAT('%', #{name}, '%')",
            "</if>",
            "<if test='startDate!=null and startDate!=&apos;&apos; and endDate!=null and endDate!=&apos;&apos;'>",
            "AND t1.try_date &gt;= #{startDate} AND t1.try_date &lt;= #{endDate}",
            "</if>",
            "ORDER BY t1.try_date ",
            "</script>"})
    List<TryJob> queryTryJobList(IPage<TryJob> page, @Param("startDate") String startDate, @Param("endDate") String endDate, @Param("type") String type, @Param("name") String name, @Param("isReview") String isReview);
*/


    //原先
  /*  @Select({"<script>",
            "SELECT s.id,s.school_id,s.subjects_id,s.need_count, " ,
            "( SELECT COUNT(*) FROM adms_try_job t WHERE t.school_id = s.school_id " ,
            "<if test='subjectsId!=null and subjectsId!=&apos;&apos; '>",
            "AND subjects_id = #{subjectsId} ",
            "</if>",
            ") AS allocateCount,",
            "getSchoolName(s.school_id) AS schoolName,",
            "t1.sort",
            "FROM",
            "adms_school_needs s JOIN adms_school_sort t1 ON t1.school_id = s.school_id",
            "where",
            "1=1",
            "<if test='subjectsId!=null and subjectsId!=&apos;&apos; '>",
            "AND s.subjects_id = #{subjectsId} ",
            "</if>",
            "AND s.need_count > ( SELECT COUNT(*) FROM adms_try_job t WHERE t.school_id = s.school_id )",
            "ORDER BY t1.sort ",
            "</script>"})
    List<TryJob> getAllocateSchoolList (@Param("subjectsId") String subjectsId);
*/

    @Select({"<script>",
            "SELECT s.id,s.school_id,s.subjects_id,s.need_count,s.station_id, " ,
            "( SELECT COUNT(*) FROM adms_try_job t WHERE t.school_id = s.school_id " ,
            "<if test='stationId!=null and stationId!=&apos;&apos; '>",
            "AND station_id = #{stationId} ",
            "</if>",
            ") AS allocateCount,",
            "getSchoolName(s.school_id) AS schoolName,",
            "t1.sort",
            "FROM",
            "adms_school_needs s JOIN adms_school_sort t1 ON t1.school_id = s.school_id",
            "where",
            "1=1",
            "<if test='stationId!=null and stationId!=&apos;&apos; '>",
            "AND s.station_id = #{stationId} ",
            "</if>",
            "AND s.need_count > ( SELECT COUNT(*) FROM adms_try_job t WHERE t.school_id = s.school_id AND  t.station_id = s.station_id )",
            "ORDER BY t1.sort ",
            "</script>"})
    List<TryJob> getAllocateSchoolList (@Param("stationId") String stationId);

    @Select({"<script>",
            "SELECT t1.*,",
            "getSchoolName(t1.school_id) AS schoolName",
            "FROM adms_try_job AS t1",
            "LEFT JOIN adms_try_job_pop_up_log AS t2 ON t1.id = t2.try_job_id",
            "AND t2.teacher_id = ${teacherId}",
            "AND t2.type = 1",
            "WHERE ",
            "1=1",
            "<if test='schoolId!=null and schoolId!=&apos;&apos; '>",
            "AND t1.school_id IN ( ${schoolId} )",
            "</if>",
            "AND t1.is_arrive_job = 0",
            "AND t1.pass_type = 0",
            "AND t2.id IS NULL ",
            "ORDER BY t1.try_date DESC ",
            "</script>"})
    List<TryJob> getTryJobPopUpList( @Param("schoolId") String schoolId, @Param("teacherId") Long teacherId);

    /**
     * 试岗通过运营主管查看提示
     */
    @Select({"<script>",
            "SELECT t1.*,",
            "getSchoolName(t1.school_id) AS schoolName",
            "FROM adms_try_job AS t1",
            "LEFT JOIN adms_try_job_pop_up_log AS t2 ON t1.id = t2.try_job_id",
            "AND t2.teacher_id = ${teacherId}",
            "<if test='subjectsId!=null and subjectsId!=&apos;&apos; '>",
            "AND t2.subjects_id IN ( ${subjectsId} )",
            "</if>",
            "AND t2.type = 1",
            "WHERE ",
            "1=1",
            "<if test='schoolId!=null and schoolId!=&apos;&apos; '>",
            "AND t1.school_id IN ( ${schoolId} )",
            "</if>",
            "<if test='subjectsId!=null and subjectsId!=&apos;&apos; '>",
            "AND t1.subjects_id IN ( ${subjectsId} )",
            "</if>",
            "AND t1.is_arrive_job = 0",
//            "AND t1.pass_type = 1",
            "AND t1.pass_type = 0",
            "AND t2.id IS NULL ",
            "ORDER BY t1.try_date DESC ",
            "</script>"})
    List<TryJob> getManagerTryJobPopUpList( @Param("schoolId") String schoolId, @Param("teacherId") Long teacherId, @Param("subjectsId") String subjectsId);


    @Select({"<script>",
            "WITH teacher_with as (select id,name,IF(role_admin.admin_id is null,1,2) as is_person from staff LEFT JOIN role_admin on staff.id = role_admin.admin_id and role_admin.role_id = 1568160915052400644 )",
            "SELECT",
            "r.id,",
            "r.inviter_id,",
            "s.name AS teacherName,",
            "COUNT(CASE WHEN r.created_at BETWEEN #{startDate} AND #{endDate} THEN 1 END) AS recruitCount, ",
            "COUNT(CASE WHEN r.invite_date_time BETWEEN #{startDate} AND #{endDate} AND r.is_arrive =1 THEN 1 END ) AS recruitInterviewCount, ",
            "COUNT(CASE WHEN r.created_at BETWEEN #{startDate} AND #{endDate} AND (r.is_arrive IS null OR r.is_arrive = 0) THEN 1 END ) AS notRecruitInterviewCount, ",
            "COUNT( CASE WHEN r.is_pass_time BETWEEN #{startDate} AND #{endDate} AND r.pass_type = 1 THEN 1 END ) AS recruitPassCount,",
            "COUNT( CASE WHEN r.is_pass_time BETWEEN #{startDate} AND #{endDate} AND r.pass_type = 2 THEN 1 END ) AS recruitNoPassCount,",
            "COUNT( CASE WHEN r.give_up_time BETWEEN #{startDate} AND #{endDate} AND r.is_give_up = 1 THEN 1 END ) AS tryJobGiveUpCount,",
            "COALESCE(SUM(tj.tryJobCount), 0) AS tryJobCount,",
            "COALESCE(SUM(tj.tryJobPassCount), 0) AS tryJobPassCount,",
            "COALESCE(SUM(tj.tryJobNoPassCount), 0) AS tryJobNoPassCount,",
            "COALESCE ( SUM( tj.notArriveCount ), 0 ) AS notArriveCount,",
            "COALESCE ( SUM( tj.alreadyTryJobCount ), 0 ) AS alreadyTryJobCount",
            "FROM",
            "adms_recruit r",
            " JOIN staff s ON s.id = r.inviter_id ",
            " JOIN adms_station_needs t1 ON t1.id = r.station_needs_id",
            "INNER JOIN teacher_with as t2 on r.inviter_id = t2.id",
            "LEFT JOIN (",
            "SELECT ",
            "tj.recruit_id,",
            "COUNT(CASE WHEN tj.try_date BETWEEN DATE(#{startDate}) AND DATE(#{endDate})  THEN 1 END) AS tryJobCount,",
            "COUNT(CASE WHEN tj.try_audit_date BETWEEN #{startDate} AND #{endDate} AND tj.pass_type = 1 THEN 1 END) AS tryJobPassCount,",
            "COUNT(CASE WHEN tj.try_audit_date BETWEEN #{startDate} AND #{endDate} AND tj.pass_type = 2 THEN 1 END) AS tryJobNoPassCount,",
            "COUNT( CASE WHEN tj.not_arrive_date BETWEEN '2024-05-01 00:00:00' AND '2025-09-01 23:59:59' AND tj.is_arrive_job = 1 THEN 1 END ) AS notArriveCount,",
            "COUNT( CASE WHEN tj.try_date BETWEEN '2024-05-01 00:00:00' AND '2025-09-01 23:59:59' AND tj.is_arrive_job = 0 THEN 1 END ) AS alreadyTryJobCount",
            "FROM adms_try_job tj",
            "where 1=1",
            "<if test='yearPart != null and yearPart != &apos;&apos;'>",
            "AND tj.year_part = ${yearPart}",
            "</if>",
            "<if test='quarterNum != null and quarterNum != &apos;&apos;'>",
            "AND tj.quarter_num = ${quarterNum}",
            "</if>",
            "GROUP BY tj.recruit_id",
            ") tj ON tj.recruit_id = r.id",
            "WHERE",
            "1 = 1",
            "<if test='theirType != null and theirType != &apos;&apos;'>",
                "AND t1.type = ${theirType}",
            "</if>",
            "<if test='subjectsId != null and subjectsId != &apos;&apos;'>",
                "AND t1.subjects_id IN ( ${subjectsId} )",
            "</if>",
            "<if test='type != null and type != &apos;&apos;'>",
            "AND t2.is_person = ${type}",
            "</if>",
            "<if test='departmentId != null and departmentId != &apos;&apos;'>",
            "AND t1.department_id = ${departmentId}",
            "</if>",
            "<if test='stationsId != null and stationsId != &apos;&apos;'>",
            "AND t1.station_id IN ( ${stationsId} )",
            "</if>",
            "<if test='labourId != null and labourId != &apos;&apos;'>",
            "AND t1.labour_id = ${labourId}",
            "</if>",
//            "<if test='yearPart != null and yearPart != &apos;&apos;'>",
//            "AND tj.year_part = ${yearPart}",
//            "</if>",
//            "<if test='quarterNum != null and quarterNum != &apos;&apos;'>",
//            "AND tj.quarter_num = ${quarterNum}",
//            "</if>",
            "GROUP BY",
            "r.inviter_id",
            "</script>"})
    public List<RecruitList> tryJobRate(@Param("startDate") String startDate,
                                        @Param("endDate") String endDate,
                                        @Param("departmentId") String departmentId,
                                        @Param("stationsId") String stationsId,
                                        @Param("labourId") String labourId,
                                        @Param("type") String type,
                                        @Param("yearPart") String yearPart,
                                        @Param("quarterNum") String quarterNum,
                                        @Param("dateType") String dateType,
                                        @Param("theirType") String theirType,
                                        @Param("subjectsId") String subjectsId
    );



    @Select({"<script>",
            "WITH teacher_with as (select id,name,IF(role_admin.admin_id is null,1,2) as is_person from staff LEFT JOIN role_admin on staff.id = role_admin.admin_id and role_admin.role_id = 1568160915052400644 )",
            "SELECT",
            "r.`name`,",
            "r.phone,",
            "r.invite_date_time,",
            "r.is_pass_time,",
            "t1.NAME AS stationName,",
            "tj.not_arrive_date,",
            "tj.try_audit_date,",
            "r.give_up_time,",
            "tj.try_date,",
            "r.created_at",
            "FROM",
            "adms_recruit r",
            "JOIN staff s ON s.id = r.inviter_id ",
            "JOIN adms_station_needs t1 ON t1.id = r.station_needs_id",
            "LEFT JOIN adms_try_job tj ON r.id = tj.recruit_id",
            "<if test='yearPart != null and yearPart != &apos;&apos;'>",
            "AND tj.year_part = ${yearPart}",
            "</if>",
            "<if test='quarterNum != null and quarterNum != &apos;&apos;'>",
            "AND tj.quarter_num = ${quarterNum}",
            "</if>",
            "INNER JOIN teacher_with as t2 on r.inviter_id = t2.id",
            "WHERE",
            "1 = 1",
            "<if test='theirType != null and theirType != &apos;&apos;'>",
                "AND t1.type = ${theirType}",
            "</if>",
            "<if test='subjectsId != null and subjectsId != &apos;&apos;'>",
                "AND t1.subjects_id IN ( ${subjectsId} )",
            "</if>",
            "<if test='type != null and type != &apos;&apos;'>",
            "AND t2.is_person = ${type}",
            "</if>",
            "<if test='departmentId != null and departmentId != &apos;&apos;'>",
            "AND t1.department_id = ${departmentId}",
            "</if>",
            "<if test='stationsId != null and stationsId != &apos;&apos;'>",
            "AND t1.station_id IN ( ${stationsId} )",
            "</if>",
            "<if test='labourId != null and labourId != &apos;&apos;'>",
            "AND t1.labour_id = ${labourId}",
            "</if>",
//            "<if test='yearPart != null and yearPart != &apos;&apos;'>",
//            "AND tj.year_part = ${yearPart}",
//            "</if>",
//            "<if test='quarterNum != null and quarterNum != &apos;&apos;'>",
//            "AND tj.quarter_num = ${quarterNum}",
//            "</if>",
            "AND r.inviter_id = ${inviterId}",
            "<if test='dateType != null and dateType != &apos;&apos; '>",
            //试岗率详情
            "<if test='dateType == 1 '>",
            //面试通过
            "<if test='recruitStatus == 0'>",
            "AND r.is_pass_time BETWEEN #{startDate} AND #{endDate} AND r.pass_type = 1",
            "</if>",
            //未到岗
            "<if test='recruitStatus == 1'>",
            "AND tj.not_arrive_date BETWEEN #{startDate} AND #{endDate}  AND tj.is_arrive_job = 1",
            "</if>",
            //已试岗
            "<if test='recruitStatus == 2'>",
            "AND tj.try_date BETWEEN #{startDate} AND #{endDate}  AND tj.is_arrive_job = 0",
            "</if>",
            "</if>",
            //试岗通过率详情
            "<if test='dateType == 2 '>",
            //面试岗人数
            "<if test='recruitStatus == 3'>",
            "AND tj.try_date BETWEEN #{startDate} AND #{endDate}",
            "</if>",
            //试岗通过
            "<if test='recruitStatus == 4'>",
            "AND tj.try_audit_date BETWEEN #{startDate} AND #{endDate} AND tj.pass_type = 1",
            "</if>",
            //试岗自弃
            "<if test='recruitStatus == 5'>",
            "AND r.give_up_time BETWEEN #{startDate} AND #{endDate} AND r.is_give_up = 1",
            "</if>",
            //试岗淘汰
            "<if test='recruitStatus == 6'>",
            "AND tj.try_audit_date BETWEEN #{startDate} AND #{endDate} AND tj.pass_type = 2",
            "</if>",
            "</if>",

            "</if>",
            "</script>"})
    public IPage<Recruit> tryJobRateDetails(IPage<?> page,
                                            @Param("startDate") String startDate,
                                            @Param("endDate") String endDate,
                                            @Param("departmentId") String departmentId,
                                            @Param("stationsId") String stationsId,
                                            @Param("labourId") String labourId,
                                            @Param("type") String type,
                                            @Param("inviterId") String inviterId,
                                            @Param("yearPart") String yearPart,
                                            @Param("quarterNum") String quarterNum,
                                            @Param("dateType") String dateType,
                                            @Param("recruitStatus") String recruitStatus,
                                            @Param("theirType") String theirType,
                                            @Param("subjectsId") String subjectsId
    );
    @Select({"<script>",
            "SELECT t1.*, sn.type AS department_type, dm.name as department_name",
            "FROM adms_try_job AS t1",
            "LEFT JOIN adms_trial_form AS r1 ON t1.id = r1.try_job_id",
            "LEFT JOIN adms_station_needs AS sn ON t1.station_needs_id = sn.id",
            "LEFT JOIN department AS dm ON sn.department_id = dm.id",
            "WHERE t1.id = ${id}",
            "LIMIT 1",
            "</script>"})
    public TryJob  getTryJobById(@Param("id") Long id);

    @Select({"<script>",
       "SELECT * from adms_try_job" ,
               " where " ,
              "school_id IN (${schoolId}) ",
            "AND subjects_id IN (${subjectsId})",
            "AND pass_type = 0 AND is_arrive_job = 0",
            "AND try_date &lt;= #{formattedDate}",
            "</script>"})
    public List<TryJob> getForceCommentList(@Param("schoolId") String schoolId,@Param("subjectsId") String subjectsId,@Param("formattedDate") String formattedDate);

}
